package com.xzhuang.task.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class DBUtil {

	// quartzDataSource
	private DriverManagerDataSource dbm = (DriverManagerDataSource) SpringContextUtil.getBean("quartzDataSource");
	
	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	public Connection getConnection() throws Exception {
		return dbm.getConnection();
	}

	/**
	 * 写任务调用日志
	 * 
	 * @param time
	 *            执行时间
	 * @param jobName
	 *            任务名称URL
	 * @param jobType
	 *            任务类型URL
	 * @param msg
	 *            日志消息
	 * @param jg
	 *            执行结果 1 成功 0 失败
	 * @throws Exception
	 */
	public void writeLog(String time, String jobName, String jobType, String msg, Integer jg) throws Exception {
		if(true) {
			return;
		}
		if (time == null || time.length() == 0) {
			throw new Exception("执行时间不能为空!");
		}
		if (jobName == null || jobName.length() == 0) {
			throw new Exception("任务名称URL不能为空!");
		}
		if (jobType == null || jobType.length() == 0) {
			throw new Exception("执行任务类型不能为空!");
		}
		if (msg == null || msg.length() == 0) {
			//throw new Exception("执行/错误 消息不能为空!");
			msg = "null";
		}
		if(jg == null){
			jg = 1;
		}
		Connection con = getConnection();
		Statement st = con.createStatement();
		if(msg != null && msg.length() > 0){
			if(msg.indexOf("(") != -1){
				msg = msg.replaceAll("\\(", "");
			}
			if(msg.indexOf(")") != -1){
				msg = msg.replaceAll("\\)", "");
			}
			if(msg.indexOf("{") != -1){
				msg = msg.replaceAll("\\{", "");
			}
			if(msg.indexOf("}") != -1){
				msg = msg.replaceAll("\\}", "");
			}
			
			if(msg.indexOf("'") != -1){
				msg = msg.replaceAll("'", "");
			}
			if(msg.indexOf("\"") != -1){
				msg = msg.replaceAll("\"", "");
			}
			
			if(msg.length() > 200) msg = msg.substring(0,200);
			
		}

		String sql = "insert into QRTZ_LOG (exeDate,exejobName,jobType,msg,exejg) values (" + "'" + time + "','" + jobName + "','" + jobType + "','" + msg + "'," + jg + ")";
		
		try {
			System.out.println(sql);
			st.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			st.close();
			con.close();
		}
	}

	/***
	 * 暂时不对LIMIT M,N 这种方式做性能优化
	 * 
	 * @param begin
	 * @param end
	 * @param jobName
	 * @param startRowNumber
	 * @param endRowNumber
	 * @return
	 * @throws Exception
	 */
	public Map searchLog(String begin, String end, String jobName, int startRowNumber, int RowCount, String sEcho) throws Exception {
		String sql = "select * from QRTZ_LOG where 1 = 1 ";
		String countsql = "select count(1) as ct from QRTZ_LOG where 1 = 1 ";
		List<List> list = new ArrayList();
		Map rmap = new HashMap();
		int count = 0;
		if (begin != null && begin.length() > 0) {
			sql = sql + " and exeDate >= '" + begin + "' ";
			countsql = countsql + " and exeDate >= '" + begin + "' ";
		}

		if (end != null && end.length() > 0) {
			sql = sql + " and exeDate <= '" + end + "' ";
			countsql = countsql + " and exeDate <= '" + end + "' ";
		}

		if (jobName != null && jobName.length() > 0) {
			sql = sql + " and exejobName like '%" + jobName + "%' ";
			countsql = countsql + " and exejobName like '%" + jobName + "%' ";
		}

		sql = sql + " order by exeDate desc limit " + startRowNumber + "," + RowCount;
		ResultSet rs = getConnection().createStatement().executeQuery(sql);

		ResultSet countrs = getConnection().createStatement().executeQuery(countsql);
		List<Object> temp;
		if (countrs != null && countrs.next()) {
			count = Integer.parseInt(countrs.getString("ct"));
			if (count > 0) {// 说明上面查询有查询结果
				while (rs.next()) {
					temp = new ArrayList();
					temp.add(rs.getString("id") == null ? "1" : rs.getString("id"));
					temp.add(rs.getString("exejobName") == null ? "" : rs.getString("exejobName"));
					temp.add(rs.getString("exeDate") == null ? "" : rs.getString("exeDate"));

					String msg = rs.getString("msg");
					if (msg == null && msg.length() == 0) {
						msg = "";
					} else {
						if (msg.length() > 100)
							msg = msg.substring(0, 100);
					}

					temp.add(msg);

					temp.add(rs.getString("exejg") == null ? "" : rs.getString("exejg"));
					list.add(temp);
				}
				rmap.put("sEcho", sEcho);
				rmap.put("iTotalDisplayRecords", count);
				rmap.put("iTotalRecords", count);
				rmap.put("aaData", list);
				// System.out.println(rmap.toString());
			} else {
				rmap.put("sEcho", sEcho);
				rmap.put("iTotalDisplayRecords", "0");
				rmap.put("iTotalRecords", "0");
				rmap.put("aaData", list);
			}
		}

		return rmap;
	}

	public String searchLogMsg(String logid) throws Exception {
		String sql = "select msg from QRTZ_LOG where id = " + logid;

		ResultSet rs = getConnection().createStatement().executeQuery(sql);
		String msg = "";
		if (rs.next()) {
			msg = rs.getString("msg");
		}

		return msg;
	}
	
	
	
	/***
	 * 
	 * @param datetime
	 * @param ip
	 * @param val
	 * @param type
	 * @throws Exception
	 */
	public void insertMonitorItem(String datetime,String ip,String val,String type) throws Exception{
		if(StringUtils.isBlank(datetime)){
			throw new  Exception("缺少参数: " + datetime);
		}
		
		if(StringUtils.isBlank(ip)){
			throw new  Exception("缺少参数: " + ip);
		}
		
		if(StringUtils.isBlank(val)){
			throw new  Exception("缺少参数: " + val);
		}
		
		if(StringUtils.isBlank(type)){
			throw new  Exception("缺少参数: " + type);
		}
		
		String sql = "INSERT INTO monitor_log ( monitor_type, monitor_ip, monitor_value, monitor_client_time, current_system_time) VALUES ('"+type+"', '"+ip+"', '"+val+"', '"+datetime+"', '"+ format.format(new Date()) +"')";
		Connection con = getConnection();
		Statement st = con.createStatement();
		
		try {
			st.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			st.close();
			con.close();
		}	
	}
	
	
	
}
